﻿using FC.Utils.Exceptions;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using System.Web;

namespace FC.Utils
{
    /// <summary>
    /// NPOI 帮助类
    /// </summary>
    public static class ExcelHelper
    {
        /// <summary>
        /// 根据 List 中的数据创建 Excel 表 (Model 的属性必须全为 string 类型)
        /// </summary>
        /// <typeparam name="T">Model 类型</typeparam>
        /// <param name="workbook">Excel 表</param>
        /// <param name="list">List</param>
        /// <returns></returns>
        public static ISheet CreateSheet<T>(this IWorkbook workbook, List<T> list, string name = "sheet") where T : class
        {
            ISheet sheet = workbook.CreateSheet(name);
            
            Type type = typeof(T);
            PropertyInfo[] properties = type.GetProperties();
            
            IRow headRow = sheet.CreateRow(0);

            ICellStyle style = workbook.CreateCellStyle();
            style.FillForegroundColor = HSSFColor.Grey40Percent.Index;  // 背景色
            style.FillPattern = FillPattern.SolidForeground;    // 

            for (int i = 0; i < properties.Length; i++)
            {
                ICell cell = headRow.CreateCell(i, CellType.String);
                cell.SetCellValue(properties[i].Name);
                cell.CellStyle = style;
            }
            for (int j = 0; j < list.Count; j++)
            {
                IRow row = sheet.CreateRow(j + 1);
                for (int i = 0; i < properties.Length; i++)
                {
                    ICell cell = row.CreateCell(i, CellType.String);
                    object o = properties[i].GetValue(list[j]);
                    cell.SetCellValue(o == null ? "" : o.ToString());
                }
            }

            return sheet;
        }

        /// <summary>
        /// 将 Excel 表中的内容转化为 List (Model 的属性必须全为 string 类型)
        /// </summary>
        /// <typeparam name="T">Model 类型</typeparam>
        /// <param name="sheet">Excel 表</param>
        /// <returns></returns>
        public static List<T> ToList<T>(this ISheet sheet) where T : class
        {
            List<T> list = new List<T>();

            if (sheet != null && sheet.LastRowNum >= 0)
            {
                Type type = typeof(T);
                PropertyInfo[] properties = type.GetProperties();
                IRow firstRow = sheet.GetRow(0);

                Dictionary<string, int> dictionary = new Dictionary<string, int>();
                for (int i = 0; i < firstRow.LastCellNum; i++)
                {
                    ICell cell = firstRow.GetCell(i);
                    if (cell != null) dictionary.Add(cell.ToString().ToUpper(), i);
                }
                string state = "";

                foreach (var property in properties)
                {
                    string key = dictionary.Keys.FirstOrDefault(p => p.Equals(property.Name, StringComparison.CurrentCultureIgnoreCase));
                    if (key == null)
                    {
                        state += $"缺少 {property.Name} 列的信息\n";
                    }
                }
                if (!string.IsNullOrWhiteSpace(state))
                    throw new ExcelHelperException(state);

                for (int i = 1; i <= sheet.LastRowNum; i++)
                {
                    IRow row = sheet.GetRow(i);
                    if (row != null)
                    {
                        T obj = Activator.CreateInstance<T>();
                        foreach (var property in properties)
                        {
                            ICell cell = row.GetCell(dictionary[property.Name.ToUpper()]);
                            property.SetValue(obj, cell == null ? null : cell.ToString(), null);
                        }
                        list.Add(obj);
                    }
                }
            }
            else
                throw new ExcelHelperException("表不能为空");
            return list;
        }
        
        /// <summary>
        /// 从文件创建 Excel 表
        /// </summary>
        /// <param name="file">文件</param>
        /// <returns></returns>
        public static IWorkbook CreateWorkbook(this HttpPostedFileBase file)
        {
            IWorkbook workbook = null;
            if (file != null && file.ContentLength > 0)
            {
                if (file.ContentType == "application/vnd.ms-excel")
                {
                    HSSFWorkbook hssfworkbook = new HSSFWorkbook(file.InputStream);
                    workbook = hssfworkbook;
                }
                else if (file.ContentType == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
                {
                    XSSFWorkbook xssfworkbook = new XSSFWorkbook(file.InputStream);
                    workbook = xssfworkbook;
                }
                else
                {
                    throw new ExcelHelperException("文件格式不正确。");
                }
            }
            else
            {
                throw new ExcelHelperException("文件不能为空。");
            }
            return workbook;
        }
    }
}
